-- specific Power Optimization details
-- this script has queries that are essentially equivalent to those in the NightWatchman 7.1 reports
-- it follows each query with a query that provides more details on those data points, especially specific computers

-- put related details for the details tables into a temp table so the rest of the queries below are not too complicated
if OBJECT_ID('tempdb..#details') is not null drop table #details
select NetbiosName, Manufacturer, Model, details.* 
into #details
from tbNWM_Report_PowerOptimization_DetailsDaily details 
  join tbAFR_Dimension_ConfigurationItem CIs on details.ConfigurationItemId=CIs.Id 
  join tbAFR_Dimension_ComputerSystem computers on details.ConfigurationItemId=computers.ConfigItemId 
  join tbAFR_Lookup_HardwareComponent hw on computers.HardwareComponentId=hw.HardwareId

-- select * from #details

-- there can be varients on the above for specific org units and locations and/or date ranges

-- Summary ------------------------------------------
-- clients with batteries
select Day, BatteryEnabledCount, TotalClientsCount from tbNWM_Report_PowerOptimization_ReportSummaryDaily where BatteryEnabledCount>0
select NetbiosName, Manufacturer, Model, Day, BatteryChemistry1, BatteryChemistry2 from #details where BatteryChemistry1<>'NA'

-- good OS
select Day, Windows8AboveOSCount, Windows81AboveOSCount from tbNWM_Report_PowerOptimization_ReportSummaryDaily where BatteryEnabledCount>0
select NetbiosName, Manufacturer, Model, Day, OperatingSystem, ServicePack from #details join tbAFR_Dimension_ComputerSystem computers on #details.ConfigurationItemId=computers.ConfigItemId
order by NetbiosName -- don't use this line if you want the default sorting (which is by day)

-- that are using the battery a lot (over 50% of the time)
select Day, UsagePercent_BatteryCount from tbNWM_Report_PowerOptimization_ReportSummaryDaily where UsagePercent_BatteryCount<>0
select NetbiosName, Manufacturer, Model, Day, UsagePercent_Battery from #details where UsagePercent_Battery>25

-- that got quite low on battery (<25%)
select Day, LowestBatteryCapacityCount from tbNWM_Report_PowerOptimization_ReportSummaryDaily where LowestBatteryCapacityCount>0
select NetbiosName, Manufacturer, Model, Day, LowestBatteryCapacity from #details where LowestBatteryCapacity>=10

-- ran out of battery
select Day, MinLowestBatteryCapacityCount from tbNWM_Report_PowerOptimization_ReportSummaryDaily where MinLowestBatteryCapacityCount>0
select NetbiosName, Manufacturer, Model, Day, LowestBatteryCapacity from #details where LowestBatteryCapacity<10
order by NetbiosName

-- misconfigured
select Day, MisconfiguredCount from tbNWM_Report_PowerOptimization_ReportSummaryDaily where MisconfiguredCount>0
select NetbiosName, Manufacturer, Model, Day, LowestBatteryCapacity from #details where LowestBatteryCapacity<10

-- connected standby capable
select Day, ConnectedStandbyEnabledCount from tbNWM_Report_PowerOptimization_ReportSummaryDaily where ConnectedStandbyEnabledCount>0
select NetbiosName, Manufacturer, Model, Day, ModernStandbyCapable from #details where ModernStandbyCapable=1

-- connected standby issues
select Day, UsingMordernStandByIncorrectlyCount from tbNWM_Report_PowerOptimization_ReportSummaryDaily where UsingMordernStandByIncorrectlyCount>0
select NetbiosName, Manufacturer, Model, Day, ActivatorActiveTimePer, ProcessorActiveTimePer, DeviceActiveTimePer, NetworkActiveTimePer, OtherActiveTimePer, DripsVariations_AC_Fast, DripsVariations_Battery_Fast, DripsVariations_AC_Slow, DripsVariations_Battery_Slow from #details
  where ActivatorActiveTimePer>0 OR ProcessorActiveTimePer>0 OR DeviceActiveTimePer>0 OR NetworkActiveTimePer>0 OR OtherActiveTimePer>0
        or DripsVariations_AC_Fast>10 or DripsVariations_Battery_Fast>10 or DripsVariations_AC_Slow>10 or DripsVariations_Battery_Slow>10

-- Battery Details ------------------------------------------
-- clients with batteries
select Day, BatteryEnabledCount, TotalClientsCount from dbo.tbNWM_Report_PowerOptimization_BatterySummaryDaily where BatteryChemistry<>'NA'
select NetbiosName, Manufacturer, Model, Day, BatteryChemistry1, BatteryChemistry2 from #details where BatteryChemistry1<>'NA'
-- that are using it a lot (over 50% of the time)
select Day, UsagePercent_BatteryCount1 'used over 50%' from dbo.tbNWM_Report_PowerOptimization_BatterySummaryDaily where UsagePercent_BatteryCount1<>0
select NetbiosName, Manufacturer, Model, Day, UsagePercent_Battery from #details where UsagePercent_Battery>25
-- or an interesting amount of time (over 10%)
select Day, UsagePercent_BatteryCount2 'used over 10%' from dbo.tbNWM_Report_PowerOptimization_BatterySummaryDaily where UsagePercent_BatteryCount2<>0
select NetbiosName, Manufacturer, Model, Day, UsagePercent_Battery from #details where UsagePercent_Battery<>0

-- not charging well (half or less)
-- when
select Day, BatteryChargeCapacityHalvedCount from dbo.tbNWM_Report_PowerOptimization_BatterySummaryDaily where BatteryChargeCapacityHalvedCount>0
-- who
select distinct NetbiosName, Manufacturer, Model, BatteryFullChargeCapacity1*100 / BatteryDesignCapacity1 'Battery 1 %' from #details where BatteryDesignCapacity1<>0 or BatteryFullChargeCapacity1<>0 order by 4 -- order by % (could also limit to those with a problem by including BatteryChargeCapacityHalvedCount>0
-- who in detail
select NetbiosName, Manufacturer, Model, BatteryFullChargeCapacity1, BatteryDesignCapacity1, BatteryFullChargeCapacity1*100 / BatteryDesignCapacity1 'Battery 1 %', BatteryFullChargeCapacity2, BatteryDesignCapacity2 from #details where BatteryDesignCapacity1<>0 or BatteryFullChargeCapacity1<>0 order by 7 -- order by %

-- draining excessively (>1% per hour while on standby)
select Day, DrainExcessiveRate from dbo.tbNWM_Report_PowerOptimization_BatterySummaryDaily where DrainExcessiveRate>0
select NetbiosName, Manufacturer, Model, Day, DrainRateAverage from #details where DrainRateAverage>0

-- ran out of battery
select Day, MinLowestBatteryCapacityCount from dbo.tbNWM_Report_PowerOptimization_BatterySummaryDaily where MinLowestBatteryCapacityCount>0
select NetbiosName, Manufacturer, Model, Day, LowestBatteryCapacity from #details where BatteryChemistry1<>'NA'

-- technology
select Day, BatteryChemistry, TotalClientsCount from dbo.tbNWM_Report_PowerOptimization_BatterySummaryDaily 
select NetbiosName, Manufacturer, Model, Day, BatteryChemistry1, BatteryChemistry2 from #details where BatteryChemistry1<>'NA'

-- recharge cycles
select Day, BatteryCycleCount1 '>500', BatteryCycleCount2 '>1000', BatteryCycleCount3 '>2000' from dbo.tbNWM_Report_PowerOptimization_BatterySummaryDaily 
select NetbiosName, Manufacturer, Model, Day, BatteryCycleCount1, BatteryCycleCount2 from #details where BatteryCycleCount1<>0

-- Connected Standby ------------------------------------------
-- connected standby capable (by model)
select Day, DeviceModel, ConnectedStandbyEnabledCount, InConnectedStandByCount 'Using connected standby', InConnectedStandByHigherCount 'Using connected standby> 25% of the time',* from tbNWM_Report_PowerOptimization_ConnectedSummaryDaily where ConnectedStandbyEnabledCount>0
select NetbiosName, Manufacturer, Model, Day, ModernStandbyCapable from #details where ModernStandbyCapable<>0

-- clients that are using connected standby (and thus those that aren't is easy enough to figure out)
select Day, DeviceModel, ConnectedStandbyEnabledCount, InConnectedStandByCount 'Using connected standby', InConnectedStandByHigherCount 'Using connected standby> 25% of the time',* from tbNWM_Report_PowerOptimization_ConnectedSummaryDaily where InConnectedStandByCount>0 or InConnectedStandByHigherCount>0
select NetbiosName, Manufacturer, Model, Day, TimeConnectedPercent_Battery, TimeConnectedPercent_AC from #details where TimeConnectedPercent_AC>0 or TimeConnectedPercent_Battery>0

-- technical issues ("Top Offenders" are active more than 10% of the time)
select Day, DeviceModel, ConnectedStandbyEnabledCount, MisconfiguredCount 'Misconfigured' from tbNWM_Report_PowerOptimization_ConnectedSummaryDaily where MisconfiguredCount>0
select NetbiosName, Manufacturer, Model, Day, ActivatorActiveTimePer, ProcessorActiveTimePer, DeviceActiveTimePer, NetworkActiveTimePer, OtherActiveTimePer from #details
  where ActivatorActiveTimePer>0 OR ProcessorActiveTimePer>0 OR DeviceActiveTimePer>0 OR NetworkActiveTimePer>0 OR OtherActiveTimePer>0

-- not using it correctly (polling too frequently or not frequently enough)
select Day, DeviceModel, ConnectedStandbyEnabledCount, SoftwarePoolingFreq, SoftwarePoolingInFreq from tbNWM_Report_PowerOptimization_ConnectedSummaryDaily where SoftwarePoolingFreq>10 or SoftwarePoolingInFreq>10
select NetbiosName, Manufacturer, Model, Day, DripsVariations_AC_Fast, DripsVariations_Battery_Fast from #details
  where (DripsVariations_AC_Good<>0 or DripsVariations_Battery_Good<>0) AND (DripsVariations_AC_Good<90 or DripsVariations_Battery_Good<90)

-- Lowest Power State (DRIPS) ------------------------------------------
-- Note: this report also uses the tbNWM_Report_PowerOptimization_ConnectedSummaryDaily - the data is strongly related

-- clients achieving DRIPS more than 80% of the time
select Day, DeviceModel, ModernStandByCapableEffectiveCount from tbNWM_Report_PowerOptimization_ConnectedSummaryDaily where ModernStandByCapableEffectiveCount<>0
select NetbiosName, Manufacturer, Model, Day, DripsPercent_AC, DripsPercent_Battery from #details where DripsPercent_AC>0 or DripsPercent_Battery>0

-- components not idling
select Day, DeviceModel, ConnectedStandbyEnabledCount, MisconfiguredCount 'Not idling' from tbNWM_Report_PowerOptimization_ConnectedSummaryDaily where MisconfiguredCount=1
select NetbiosName, Manufacturer, Model, Day, ActivatorActiveTimePer, ProcessorActiveTimePer, DeviceActiveTimePer, NetworkActiveTimePer, OtherActiveTimePer from #details
  where ActivatorActiveTimePer>0 OR ProcessorActiveTimePer>0 OR DeviceActiveTimePer>0 OR NetworkActiveTimePer>0 OR OtherActiveTimePer>0

-- polling frequencies too fast or too slow
select Day, DeviceModel, ConnectedStandbyEnabledCount, SoftwarePoolingFreq 'Polling too frequently' from tbNWM_Report_PowerOptimization_ConnectedSummaryDaily where SoftwarePoolingFreq>0
select NetbiosName, Manufacturer, Model, Day, DripsVariations_AC_Fast, DripsVariations_Battery_Fast from #details
  where DripsVariations_AC_Fast>10 or DripsVariations_Battery_Fast>10
select Day, DeviceModel, ConnectedStandbyEnabledCount, SoftwarePoolingFreq 'Polling too infrequently' from tbNWM_Report_PowerOptimization_ConnectedSummaryDaily where SoftwarePoolingFreq>0
select NetbiosName, Manufacturer, Model, Day, DripsVariations_AC_Slow, DripsVariations_Battery_Slow from #details
  where DripsVariations_AC_Slow>10 or DripsVariations_Battery_Slow>10

-------------------------------------------------------------------------
-- when is the data flowing in?

-------------------------------------------------------------------------
-- alternate ways to build #details



-- a report ida: reboots for pending reboots where not all updates applied enterprise vs. professional vs. others

